﻿Public Class UCLapBaoCao
    Dim connect As New connect()
    Private Sub UCLapBaoCao_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        LoadcmbNam()
        LoadcmbLoai()
    End Sub
    Private Sub LoadcmbNam()
        Dim i As Integer
        Dim dt As DataTable = New DataTable("NAM")
        dt.Columns.Add("ID")
        dt.Columns.Add("NAM")

        For i = Convert.ToInt32(DateTime.Now.Year.ToString("0000")) To 2005 Step -1
            dt.Rows.Add(i, i)
        Next
        Dim ds As New DataSet("NAM")
        ds.Tables.Add(dt)
        With cmbNam
            .DataSource = ds.Tables("NAM")
            .DisplayMember = "NAM"
            .ValueMember = "ID"
        End With
        cmbNam.SelectedValue = Convert.ToInt32(DateTime.Now.Year.ToString("0000"))
    End Sub
    Private Sub LoadcmbThang()
        Dim i As Integer
        Dim dt As DataTable = New DataTable("THANG")
        dt.Columns.Add("ID")
        dt.Columns.Add("THANG")
        If (cmbNam.SelectedValue.ToString() = DateTime.Now.Year.ToString("0000")) Then
            For i = 1 To Convert.ToInt32(DateTime.Now.Month.ToString("00")) Step 1
                dt.Rows.Add(i, i)
            Next
        Else
            For i = 1 To 12 Step 1
                dt.Rows.Add(i, i)
            Next
        End If
        Dim ds As New DataSet("THANG")
        ds.Tables.Add(dt)
        With cmbThang
            .DataSource = ds.Tables("THANG")
            .DisplayMember = "THANG"
            .ValueMember = "ID"
        End With
        cmbThang.SelectedValue = Convert.ToInt32(DateTime.Now.Month.ToString("00"))
    End Sub
    Private Sub LoadcmbLoai()
        Dim ds As New DataSet()
        ds = connect.LoadLoaiPhong("select MALP,TENLP from LOAIPHONG")
        With cmbLoai
            .DataSource = ds.Tables("LOAIPHONG")
            .DisplayMember = "TENLP"
            .ValueMember = "MALP"
        End With
    End Sub
    Private Sub cmbNam_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbNam.SelectedIndexChanged
        LoadcmbThang()
    End Sub
    Private Function TaoMaBC() As String
        Dim yy As Integer = DateTime.Today.Year Mod 100
        Dim mm As Integer = DateTime.Today.Month

        Dim ngay As String = yy.ToString() + mm.ToString("00")
        Dim dt As DataTable = connect.GetData("select * from BAOCAO where MABC like '" + ngay + "%'")
        Dim stt As Integer = dt.Rows.Count() + 1
        Dim str_stt As String = stt.ToString("000")
        Dim maBC As String = ngay + str_stt
        Dim check As Boolean = False
        While (check = False)
            Dim dt2 As DataTable = connect.GetData("select * from BAOCAO where MABC = '" + maBC + "'")
            If (dt2.Rows.Count() > 0) Then
                stt = stt + 1
                str_stt = stt.ToString("000")
                maBC = ngay + str_stt
                Dim dt3 As DataTable = connect.GetData("select * from BAOCAO where MABC ='" + maBC + "'")
                If (dt3.Rows.Count() > 0) Then
                    check = False
                Else
                    check = True
                End If
            Else
                check = True
            End If
        End While
        Return maBC
    End Function
    Private Sub btnLap_Click(sender As System.Object, e As System.EventArgs) Handles btnLap.Click
        If (cmbThang.SelectedValue = "") Then
            MessageBox.Show("Bạn vui lòng chọn tháng")
            Return
        Else
            Dim maBC = TaoMaBC()
            Dim yyyy As Integer = DateTime.Today.Year
            Dim mm As Integer = DateTime.Today.Month
            Dim dd As Integer = DateTime.Today.Day

            Dim ngay As String = yyyy.ToString("0000") + "-" + mm.ToString("00") + "-" + dd.ToString("00")
            Dim y As Integer = Convert.ToInt32(cmbNam.SelectedValue.ToString()) Mod 100
            Dim nam As String = (Convert.ToInt32(cmbNam.SelectedValue Mod 100)).ToString()
            Dim thang As String = (Convert.ToInt32(cmbThang.SelectedValue)).ToString("00")
            Dim head As String = nam + thang
            Dim loai As Integer = Convert.ToInt32(cmbLoai.SelectedValue)
            Dim sql1 = "select * from TINDANG where MATD like '" + head + "%' and MALP=" + loai.ToString()
            Dim sql2 = "select distinct MAPT from PHIEUTHUE pt, TINDANG td where td.MATD=pt.MAPT and pt.MAPT like '" + head + "%' and td.MALP=" + loai.ToString()
            Dim dt1, dt2 As New DataTable
            dt1 = connect.GetData(sql1)
            dt2 = connect.GetData(sql2)
            Dim so1 As Integer = dt1.Rows.Count()
            Dim so2 As Integer = dt2.Rows.Count()
            If (so1 + so2 <= 0) Then
                MessageBox.Show("Tháng này chưa có số liệu mới nên không thể lập báo cáo")
                Return
            End If
            Dim phantram1 As Double = Convert.ToDouble((so1 / (so1 + so2)) * 100)
            Dim phantram2 As Double = Convert.ToDouble((so2 / (so1 + so2)) * 100)
            Dim sql As String = "insert into BAOCAO(MABC,MALP,NGAYLAP,LAPCHOTHANG,SOLUOTDANG,PHANTRAM1,SOLUOTTHUE,PHANTRAM2) values('" + maBC + "'," + loai.ToString() + ",'" + ngay.ToString() + "',N'Lập cho tháng " + cmbThang.SelectedValue.ToString() + " năm " + cmbNam.SelectedValue.ToString() + "'," + so1.ToString() + "," + phantram1.ToString() + "," + so2.ToString() + "," + phantram2.ToString() + ")"
            connect.ExecSql(sql)
            MessageBox.Show("Đã lập thành công và lưu vào cơ sở dữ liệu!")
        End If

    End Sub
End Class
